﻿using System;
using System.Data;
using System.Text;
using Yunlib.Common;

namespace Yunlib.DAL
{
    public class ReaderInfoDAL
    {
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="rdid">读者条码</param>
        /// <param name="rdpwd">读者登录密码</param>
        /// <returns></returns>
        public DataTable UserLogin(string rdid)
        {
            //简单过滤非法字符
            if (rdid.Contains("'") || rdid.Contains("-") || rdid.Contains("drop table"))
            {
                return null;
            }
            

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" SELECT 读者条码 AS DZ_CODE,借书证号 AS LEND_CODE ,姓名 AS DZ_NAME,性别 AS DZ_GENDER,身份证号 AS ID_CARD,读者级别 AS DZ_LEVEL,单位 AS DZ_UNIT,电话 AS DZ_PHONE,联系地址 AS DZ_ADDRESS,Email AS DZ_EMAIL,押金 AS DZ_YAJIN,可外借 AS CAN_LEND,已外借 AS HAS_LEND,发证日期 as REGISTR_TIME,失效日期 AS LOSE_TIME,欠罚款 AS DUE_MONEY,欠赔款 AS UNDER_MONEY ,密码 AS PASS_WORD,民族 AS NATION FROM 读者库 ");
            sqlStr.AppendLine(@" WHERE");
            sqlStr.AppendLine(@" 读者条码='{0}'".FormatWith(rdid));

            DataTable dt = SqlServerHelper.ExecuteDataTable(sqlStr.ToString());

            return dt;
        }

        public DataTable GetDzByLendBookCode(string barcode)
        {
            var sqlStr = @"SELECT a.读者条码 AS DZ_CODE,b.借书证号 AS LEND_CODE ,b.姓名 AS DZ_NAME,b.性别 AS DZ_GENDER,b.身份证号 AS ID_CARD,b.读者级别 AS DZ_LEVEL,b.单位 AS DZ_UNIT,
	            b.电话 AS DZ_PHONE,b.联系地址 AS DZ_ADDRESS,b.Email AS DZ_EMAIL,b.押金 AS DZ_YAJIN,b.可外借 AS CAN_LEND,b.已外借 AS HAS_LEND,b.失效日期 AS LOSE_TIME,b.欠罚款 AS DUE_MONEY,
		            b.欠赔款 AS UNDER_MONEY ,b.密码 AS PASS_WORD,b.民族 AS NATION FROM 流通库 a left join 读者库 b on a.读者条码 = b.读者条码 where a.条形码 = '{0}'".FormatWith(barcode);
            DataTable dt = SqlServerHelper.ExecuteDataTable(sqlStr.ToString());

            return dt;
        }


        /// <summary>
        /// 获取用户可外借数和已外借数
        /// </summary>
        /// <param name="rdid"></param>
        /// <param name="rdpwd"></param>
        /// <returns></returns>
        public DataTable GetUser(string rdid)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" SELECT * FROM [dbo].[读者库]");
            sqlStr.AppendLine(@" WHERE");
            sqlStr.AppendLine(@" 读者条码='{0}'".FormatWith(rdid));

            DataTable dt = SqlServerHelper.ExecuteDataTable(sqlStr.ToString());

            return dt;
        }

        public DataTable GetUserByReaderCode(string rdid)

        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" SELECT 可外借,已外借,读者条码,密码 FROM [dbo].[读者库]");
            sqlStr.AppendLine(@" WHERE");
            sqlStr.AppendLine(@" 读者条码='{0}'".FormatWith(rdid));

            return SqlServerHelper.ExecuteDataSet(sqlStr.ToString()).Tables[0];
        }



        /// <summary>
        /// 更新读者已借阅数量
        /// </summary>
        /// <param name="borrowCount"></param>
        /// <returns></returns>
        public bool UpdateUserBoorowNum(int borrowCount, string username)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" UPDATE 读者库 SET 已外借={0}".FormatWith(borrowCount));
            sqlStr.AppendLine(@" WHERE 读者条码='{0}'".FormatWith(username));

            int s = SqlServerHelper.ExecuteNonQuery(sqlStr.ToString());

            if (s > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UserReportLoss(string dz_cod)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" UPDATE 读者库 SET 挂失注销='挂失',挂失日期='{0}'".FormatWith(DateTime.Now));
            sqlStr.AppendLine(@" WHERE 读者条码='{0}'".FormatWith(dz_cod));

            int s = SqlServerHelper.ExecuteNonQuery(sqlStr.ToString());

            if (s > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
